﻿using System;
using System.Collections.Generic;
using System.ComponentModel;
using System.Data;
using System.Drawing;
using System.Text;
using System.Windows.Forms;
//using System.Data;
using System.Data.SqlClient;
using System.Globalization;

using CNCSoft.EWinform;
using CNCSoft.EWinform.ControlUtils;
using CNCSoft.EWinform.AGrid;
using CNCSoft.EData;
using Common;


namespace CNCSoft.ErpApps.TMS
{
    public partial class frmDoiChieuPortal : Form
    {

        private BindingSource bindingSource1 = new BindingSource();
        private SqlDataAdapter dataAdapter = new SqlDataAdapter();
        DataGridStyleFactory dgridSF = new DataGridStyleFactory();

        
        public frmDoiChieuPortal()
        {
            InitializeComponent();
        }


        private void cmdXem_Click(object sender, EventArgs e)
        {
            // Reload the data from the database.
            string strMMDDYYYY_TuNgay = txtTuNgay.getDateValue().Month.ToString("00") + "-" + txtTuNgay.getDateValue().Day.ToString("00") + "-" + txtTuNgay.getDateValue().Year.ToString("0000");
            string strMMDDYYYY_DenNgay = txtDenNgay.getDateValue().Month.ToString("00") + "-" + txtDenNgay.getDateValue().Day.ToString("00") + "-" + txtDenNgay.getDateValue().Year.ToString("0000");
            if (chkChuaNhap.Checked)
                GetData("SELECT TienMat_Portal.SoChungTu, Convert(varchar, TienMat_Portal.NgayChungTu, 103) as NgayChungTu, " +
                        "Invoice.InvoiceNumber, HoLot + ' ' + Ten as HoTen, Isnull(SoTien,0) as SoTien, SoReceiptPortal, SoInvoicePortal, GhiChu " +
                        "FROM TienMat_Portal " +
                        "LEFT JOIN TienMat ON TienMat_Portal.SoChungTu = TienMat.SoChungTu " +
                        "LEFT JOIN Invoice ON TienMat.MaDoiTac = Invoice.InvoiceNumber " +
                        "LEFT JOIN InvoiceDetail ON TienMat.MaDoiTac = InvoiceDetail.InvoiceNumber AND TienMat.SoLanTrenInvoice = InvoiceDetail.SoLan " +
                        "LEFT JOIN Enquiry ON Invoice.EnquiryNumber = Enquiry.EnquiryNumber " +
                        "WHERE (TienMat_Portal.NgayChungTu BETWEEN '" + strMMDDYYYY_TuNgay + "' AND '" + strMMDDYYYY_DenNgay + "') " +
                        "AND (MaLoaiChungTu IN ('HP', 'PK', 'NH') ) " + 
                        "AND (SoReceiptPortal = '' OR SoInvoicePortal = '' ) " +
                        "ORDER BY TienMat_Portal.NgayChungTu, Invoice.InvoiceNumber ");
            else
                GetData("SELECT TienMat_Portal.SoChungTu, Convert(varchar, TienMat_Portal.NgayChungTu, 103) as NgayChungTu, " +
                        "Invoice.InvoiceNumber, HoLot + ' ' + Ten as HoTen, Isnull(SoTien,0) as SoTien, SoReceiptPortal, SoInvoicePortal, GhiChu " +
                        "FROM TienMat_Portal " +
                        "LEFT JOIN TienMat ON TienMat_Portal.SoChungTu = TienMat.SoChungTu " +
                        "LEFT JOIN Invoice ON TienMat.MaDoiTac = Invoice.InvoiceNumber " +
                        "LEFT JOIN InvoiceDetail ON TienMat.MaDoiTac = InvoiceDetail.InvoiceNumber AND TienMat.SoLanTrenInvoice = InvoiceDetail.SoLan " +
                        "LEFT JOIN Enquiry ON Invoice.EnquiryNumber = Enquiry.EnquiryNumber " +
                        "WHERE (TienMat_Portal.NgayChungTu BETWEEN '" + strMMDDYYYY_TuNgay + "' AND '" + strMMDDYYYY_DenNgay + "') " +
                        "AND (MaLoaiChungTu IN ('HP', 'PK', 'NH') ) " +
                        "ORDER BY TienMat_Portal.NgayChungTu, Invoice.InvoiceNumber " );
            //GetData(dataAdapter.SelectCommand.CommandText);
        }

        private void cmdCapNhat_Click(object sender, EventArgs e)
        {
            try
            {
                // Update the database with the user's changes.
                dataAdapter.SelectCommand.CommandText = "SELECT [SoChungTu],[NgayChungTu],[SoReceiptPortal],[SoInvoicePortal],[GhiChu] FROM TienMat_Portal";
                dataAdapter.Update((DataTable)bindingSource1.DataSource);
            }
            catch (Exception ex)
            {
                MessageBox.Show(ex.Message);
            }
        }


        private void GetData(string selectCommand)
        {
            //MessageBox.Show(selectCommand);
            try
            {
                // Create a new data adapter based on the specified query.
                dataAdapter = new SqlDataAdapter(selectCommand, GlobalVariable.WSInfo.ConnectionString);

                // Create a command builder to generate SQL update, insert, and
                // delete commands based on selectCommand. These are used to
                // update the database.
                SqlCommandBuilder commandBuilder = new SqlCommandBuilder(dataAdapter);

                // Populate a new data table and bind it to the BindingSource.
                DataTable table = new DataTable();
                //table.Locale = System.Globalization.CultureInfo.InvariantCulture;
                dataAdapter.Fill(table);
                bindingSource1.DataSource = table;

                // Resize the DataGridView columns to fit the newly loaded content.
                //dgridDoiChieu.AutoResizeColumns(
                //    DataGridViewAutoSizeColumnsMode.AllCellsExceptHeader);
            }
            catch (SqlException)
            {
                MessageBox.Show("Error: \n" + selectCommand);
            }
        }

        private void frmDoiChieuPortal_Load(object sender, EventArgs e)
        {
            //Insert so lieu doi chieu cho Portal tu TIENMAT
            SQLHelper.ExecuteNonQuery(GlobalVariable.WSInfo.ConnectionString, " INSERT INTO TienMat_Portal SELECT SoChungTu, NgayChungTu, '', '', '' " +
                                                                            "FROM TienMat " +
                                                                            "WHERE SoChungTu NOT IN (SELECT SoChungTu FROM TienMat_Portal)");


            txtTuNgay.Text = DateTime.Now.Date.AddDays(-DateTime.Now.Date.Day + 1).ToString("dd/MM/yyyy");
            txtDenNgay.Text = GlobalVariable.WSInfo.WorkingDate.ToString("dd/MM/yyyy");

            // Bind the DataGridView to the BindingSource
            // and load the data from the database.
            dgridDoiChieu.DataSource = bindingSource1;

            string strMMDDYYYY_TuNgay = txtTuNgay.getDateValue().Month.ToString("00") + "-" + txtTuNgay.getDateValue().Day.ToString("00") + "-" + txtTuNgay.getDateValue().Year.ToString("0000");
            string strMMDDYYYY_DenNgay = txtDenNgay.getDateValue().Month.ToString("00") + "-" + txtDenNgay.getDateValue().Day.ToString("00") + "-" + txtDenNgay.getDateValue().Year.ToString("0000");
            GetData("SELECT TienMat_Portal.SoChungTu, Convert(varchar, TienMat_Portal.NgayChungTu, 103) as NgayChungTu, " +
                    "Invoice.InvoiceNumber, HoLot + ' ' + Ten as HoTen, Isnull(SoTien,0) as SoTien, SoReceiptPortal, SoInvoicePortal, GhiChu " +
                    "FROM TienMat_Portal " +
                    "LEFT JOIN TienMat ON TienMat_Portal.SoChungTu = TienMat.SoChungTu " +
                    "LEFT JOIN Invoice ON TienMat.MaDoiTac = Invoice.InvoiceNumber " +
                    "LEFT JOIN InvoiceDetail ON TienMat.MaDoiTac = InvoiceDetail.InvoiceNumber AND TienMat.SoLanTrenInvoice = InvoiceDetail.SoLan " +
                    "LEFT JOIN Enquiry ON Invoice.EnquiryNumber = Enquiry.EnquiryNumber " +
                    "WHERE (TienMat_Portal.NgayChungTu BETWEEN '" + strMMDDYYYY_TuNgay + "' AND '" + strMMDDYYYY_DenNgay + "') " +
                    "AND MaLoaiChungTu IN ('HP', 'PK', 'NH') " +
                    "ORDER BY TienMat_Portal.NgayChungTu, Invoice.InvoiceNumber "
                    );
            
            buildDgridTS();
        }

        private void buildDgridTS()
        {
            try
            {
                DataGridTableStyle ts = new DataGridTableStyle();
                ts.MappingName = bindingSource1.DataSource.ToString();

                AGridTextBoxColumn agridTC;

                //Them cot Ma HV
                agridTC = new AGridTextBoxColumn();
                agridTC.MappingName = "InvoiceNumber";
                agridTC.HeaderText = "Mã học viên";
                agridTC.NullText = "";
                agridTC.Width = 100;
                agridTC.Alignment = HorizontalAlignment.Center;
                agridTC.ReadOnly = true;
                ts.GridColumnStyles.Add(agridTC);
                
                //SoChungtu KT
                //Them cot Ma HV
                agridTC = new AGridTextBoxColumn();
                agridTC.MappingName = "SoChungTu";
                agridTC.HeaderText = "Số phiếu thu";
                agridTC.NullText = "";
                agridTC.Width = 100;
                agridTC.Alignment = HorizontalAlignment.Center;
                agridTC.ReadOnly = true;
                ts.GridColumnStyles.Add(agridTC);
                

                //Ngay chung tu
                AGridDateTimeColumn colNgay = new AGridDateTimeColumn();
                colNgay.DateTimePicker.CustomFormat = "dd/MM/yyyy";
                colNgay.DateTimePicker.Format = DateTimePickerFormat.Custom;
                colNgay.DateTimePicker.Visible = false;
                colNgay.MappingName = "NgayChungTu";
                colNgay.HeaderText = "Ngày";
                colNgay.NullText = "";
                colNgay.Width = 100;
                colNgay.Alignment = HorizontalAlignment.Center;
                colNgay.ReadOnly = true;
                ts.GridColumnStyles.Add(colNgay);

                //So ReceiptPortal
                agridTC = new AGridTextBoxColumn();
                agridTC.MappingName = "HoTen";
                agridTC.HeaderText = "Họ Tên";
                agridTC.NullText = "";
                agridTC.Width = 150;
                agridTC.Alignment = HorizontalAlignment.Left;
                agridTC.ReadOnly = true;
                ts.GridColumnStyles.Add(agridTC);

                //SoTien
                agridTC = new AGridTextBoxColumn();
                agridTC.MappingName = "SoTien";
                agridTC.HeaderText = "Số tiền";
                agridTC.NullText = "";
                agridTC.Width = 100;
                agridTC.Format = "N";
                agridTC.Alignment = HorizontalAlignment.Right;
                agridTC.ReadOnly = true;
                ts.GridColumnStyles.Add(agridTC);
                
                //So ReceiptPortal
                agridTC = new AGridTextBoxColumn();
                agridTC.MappingName = "SoReceiptPortal";
                agridTC.HeaderText = "Số ReceiptPortal";
                agridTC.NullText = "";
                agridTC.Width = 150;
                agridTC.Alignment = HorizontalAlignment.Center;
                ts.GridColumnStyles.Add(agridTC);

                //So InvoicePortal
                agridTC = new AGridTextBoxColumn();
                agridTC.MappingName = "SoInvoicePortal";
                agridTC.HeaderText = "Số InvoicePortal";
                agridTC.NullText = "";
                agridTC.Width = 150;
                agridTC.Alignment = HorizontalAlignment.Center;
                ts.GridColumnStyles.Add(agridTC);

                //So GhiChu
                agridTC = new AGridTextBoxColumn();
                agridTC.MappingName = "GhiChu";
                agridTC.HeaderText = "Ghi chú";
                agridTC.NullText = "";
                agridTC.Width = dgridDoiChieu.Width - 920;
                agridTC.Alignment = HorizontalAlignment.Left;
                ts.GridColumnStyles.Add(agridTC);

                dgridDoiChieu.TableStyles.Clear();
                dgridDoiChieu.TableStyles.Add(ts);
                dgridSF.applyStyle(dgridDoiChieu, DataGridStyleFactory.Styles.SexyStyle);
                dgridDoiChieu.TableStyles[0].AllowSorting = false;
            }
            catch { }

        }

        private void chkChuaNhap_CheckedChanged(object sender, EventArgs e)
        {
            cmdXem_Click(cmdXem, e);
        }

        private void frmDoiChieuPortal_Resize(object sender, EventArgs e)
        {
            buildDgridTS();
        }

    }
}
